﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace tsgl
{
    class loanClass
    {
        public static DataTable getBorrowerInfo(string borrowerId)
        {
            //borrowerId = "";
            string strSql;
            if (borrowerId == "")
            {
                strSql = "select 借阅者编号,姓名,借阅者类型,借书证状态,最大借书数量,最长借书期限,押金剩余,借书证状态 from borrowerView where 借书证状态='有效'";
            }
            else
            {
                strSql = "select 借阅者编号,姓名,借阅者类型,借书证状态,最大借书数量,最长借书期限,押金剩余,借书证状态 from borrowerView where 借阅者编号="+borrowerId+"";
            }
            return bookDbClass.ExecuteDataTable(strSql);
        }

        public static DataTable getBookInfo(string bookBarcode)
        {
            //bookBarcode="";
            string strSql;
            if (bookBarcode == "")
            {
                strSql = "select bookView.图书条码,bookView.书目编号,bookView.图书名称,bookView.价格,bookView.出版社名称,bookView.作者,bookView.总藏书数量,bookView.现存数量 from bookView,图书信息 where bookView.图书条码=图书信息.图书条码 and 图书信息.图书状态='在藏'";
            }
            else
            {
                strSql = "select 图书条码,书目编号,图书名称,价格,出版社名称,作者,总藏书数量,现存数量 from bookView where 图书条码="+bookBarcode+"";
            }
            return bookDbClass.ExecuteDataTable(strSql);
        }

        public static int getLoanBookNums(string borrowerId)
        {
            string commStr = "select count(*) from 借阅信息 where 借阅者编号="+borrowerId+"";
            return bookDbClass.ExecuteNonQurey(commStr);
        }

        public static DataTable getLoanInfo(string borrowerId)
        {
            //borrowerId = "";
            if (borrowerId == "")
            {
                return bookDbClass.ExecuteDataTable("select 借阅者编号,姓名,图书条码,图书名称,借出日期,应还日期,续借次数,图书借阅员 from loanView");
            }
            else
            {
                return bookDbClass.ExecuteDataTable("select 借阅者编号,姓名,图书条码,图书名称,借出日期,应还日期,续借次数,图书借阅员 from loanView where 借阅者编号="+borrowerId+" order by 借阅编号");
            }
        }

        public static DataTable getOverdureInfo(string borrowerId)
        {
            string str = "select 借阅者编号,姓名,图书条码,图书名称,价格,借出日期,应还日期,续借次数,图书借阅员,书目编号 from loanView where 应还日期 < "+DateTime.Now.ToShortDateString()+" and 借阅者编号="+borrowerId+"";
            return bookDbClass.ExecuteDataTable(str);
        }

        public static Boolean isOverdue(string borrowerId)
        {
            DataTable dt = getOverdureInfo(borrowerId);
            if (dt.Rows.Count != 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        public static Boolean loanAdd(string borrowerId, string bookBarcode, DateTime loanDate, DateTime returenDate, int loanNums, string loanerName)
        {
        //    try
        //    {
                //string strInsertComm;
                //strInsertComm = "insert into 借阅信息(借阅者编号,图书条码,借出日期,应还日期,续借次数,图书借阅员) values(@a,@b,@c,@d,@e,@f)";
            //bookDbClass.ExecuteNonQurey("insert into 借阅信息(借阅者编号,图书条码,借出日期,应还日期,续借次数,图书借阅员) values(@a,@b,@c,@d,@e,@f)", new SqlParameter("a", borrowerId), new SqlParameter("b", bookBarcode), new SqlParameter("c", loanDate), new SqlParameter("d", returenDate), new SqlParameter("e", loanNums), new SqlParameter("f", loanerName));
            bookDbClass.ExecuteNonQurey("insert into 借阅信息(借阅者编号,图书条码,借出日期,应还日期,续借次数,图书借阅员) values("+borrowerId+","+bookBarcode+","+loanDate+","+returenDate+","+loanNums+","+loanerName+")");   
            return true;
            //}
            //catch (Exception)
            //{
            //    return false;
            //}
        }

        //public static Boolean bookNowNumReduce(string bibliothecaId)
        //{
        ////    try
        //    {
        //        string strEditComm;
        //        strEditComm = "update 书目信息 set 现存数量=现存数量-1 where 书目编号=" + bibliothecaId + "";
        //        bookDbClass.ExecuteNonQurey(strEditComm);
        //        return true;
        //    }
        //    catch(Exception)
        //    {
        //        return false;
        //    }
        //}

        public static Boolean setBookState(string bookBarcode, string bookState)
        {
            try
            {
                string strSql;
                strSql = "update 图书信息 set 图书状态=" + bookState + " where 图书条码=" + bookBarcode + "";
                bookDbClass.ExecuteNonQurey(strSql);
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public static DataTable getBorrowerInfo2(string borrowerId)
        {
            string str;
            if (borrowerId == "")
            {
                str = "select * from borrowerView where 借阅者编号 in (select distinct 借阅者编号 from 借阅信息)";
            }
            else
            {
                str = "select * from borrowerView where 借阅者编号='"+borrowerId+"'";
            }
            return bookDbClass.ExecuteDataTable(str);
        }

        //public static DataTable getLoanInfo(string borrowrId)
        //{
        //    if (borrowrId == "")
        //    {
        //        string str = "select 借阅者编号,姓名,图书条码,书目编号,图书名称,价格,借出日期,应还日期,续借次数,图书借阅员 from loanView";
        //        return bookDbClass.ExecuteDataTable(str);
        //    }
        //    else
        //    {
        //        string str = "select 借阅者编号,姓名,图书条码,书目编号,图书名称,价格,借出日期,应还日期,续借次数,图书借阅员 from loanView where 借阅者编号='" + borrowrId + "' order by 借阅编号";
        //        return bookDbClass.ExecuteDataTable(str);
        //    }
        //}


        //public static DataTable getOverdueInfo(string borrowerId)
        //{
        //    string str;
        //    if (borrowerId != "")
        //    {
        //        str = "select 借阅者编号,姓名,图书条码,价格,借出日期,应还日期,续借次数,图书借阅员,书目编号 form loanView where 应还日期 < '" + DateTime.Now.Date.ToShortDateString() + "' and 借阅者编号 ='" + borrowerId + "'";

        //    }
        //    else
        //    {
        //        str = "select 借阅者编号,姓名,图书条码,价格,借出日期,应还日期,续借次数,图书借阅员,书目编号 form loanView where 应还日期 < '" + DateTime.Now.Date.ToShortDateString() + "'";
        //    }
        //    return bookDbClass.ExecuteDataTable(str);
        //}

        public static Boolean loanDelete(string borrowerId,string bookCode)
        {
            try
            {
                string str = "delete from 借阅信息 where 借阅者编号='" + borrowerId + "' and 图书条码='" + bookCode + "'";
                bookDbClass.ExecuteDataTable(str);
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        //public static Boolean setBookState(string bookBarcode, string bookState)
        //{
        //    try
        //    {
        //        string str = "update 图书信息 set 图书状态='" + bookState + "' where 图书条码='" + bookBarcode + "'";
        //        return bookDbClass.ExecuteDataTable(str);
        //        return true;
        //    }
        //    catch (Exception)
        //    {
        //        return false;
        //    }
        //}

        public static int bookNowNumAdd(string bibliothecaId)
        {
            string str="update 书目信息 set 现存数量 =现存数量+1 where 书目编号='"+bibliothecaId+"'";
            return bookDbClass.ExecuteNonQurey(str);
        }

        public static int bookNowNumReduce(string bibliothecaId)
        {
            string str = "update 书目信息,set 现存数量 =现存数量-1 where 书目编号='" + bibliothecaId + "'";
            return bookDbClass.ExecuteNonQurey(str);
        }

        public static int bookTotalNumReduce(string bibliothceaId)
        {
            string str = "update 书目信息 set 总藏书量=总藏书量-1 where 书目编号='"+bibliothceaId+"'";
            return bookDbClass.ExecuteNonQurey(str);
        }
    }
}
